-- mb260_11372506_agilcont


CREATE TABLE common_Empresa
(
	EmpresaId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	Ruc VARCHAR(50),
	RazonSocial VARCHAR(50),
	Logo VARCHAR(50),
	EstaActivo bit,
	CreadoPorUserId uniqueidentifier NULL,
	UNIQUE(Codigo),
	PRIMARY KEY (EmpresaId)
	-- ,FOREIGN KEY (UserManagementId) REFERENCES common_User(UserId) 
	-- ON DELETE NO ACTION 
	-- ON UPDATE NO ACTION
) ;

		
CREATE TABLE common_Agencia
(
	AgenciaId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	NombreComun VARCHAR(50),
	Direccion VARCHAR(50),
	EsSede bit,
	EstaActivo bit,
	EmpresaId uniqueidentifier NOT NULL,
	UNIQUE(EmpresaId, Codigo),
	PRIMARY KEY (AgenciaId),
	FOREIGN KEY (EmpresaId) REFERENCES common_Empresa(EmpresaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;

		
CREATE TABLE common_User
(
	UserId uniqueidentifier NOT NULL,
	UserLogin VARCHAR(50),
	UserPassword VARCHAR(50),
	Email VARCHAR(50),
	Name VARCHAR(50),
	IsActive bit,
	IsExpiredPwd bit,
	CreationDate DATETIME,
	AgenciaId uniqueidentifier NOT NULL,
	UNIQUE(UserLogin),
	PRIMARY KEY (UserId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;
-- INSERT INTO common_User(UserId, Name, UserLogin, UserPassword, Email, IsActive, IsExpiredPwd, CreationDate) 
		-- VALUES (NEWID(), 'Angel Sullon','asullom','DjZcPFY3','asullom@hotmail.com','True', 'False', GETDATE())
		-- ;		
		
CREATE TABLE common_Centro_Costo
(
	CentroCostoId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	Nombre VARCHAR(50),
	CentroCostoPrincipal VARCHAR(50),
	Nivel INT,
	AgenciaId uniqueidentifier NOT NULL,
	Parent uniqueidentifier NULL,
	UNIQUE(AgenciaId, Codigo),
	PRIMARY KEY (CentroCostoId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (Parent) REFERENCES common_Centro_Costo(CentroCostoId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;

CREATE TABLE common_Cuenta
(
	CuentaId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	Nombre VARCHAR(50),
	CuentaTipo VARCHAR(50),
	CuentaPrincipal VARCHAR(50),
	Nivel INT,
	AgenciaId uniqueidentifier NOT NULL,
	Parent uniqueidentifier NULL,
	UNIQUE(AgenciaId, Codigo),
	PRIMARY KEY (CuentaId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (Parent) REFERENCES common_Cuenta(CuentaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;


CREATE TABLE common_Ejercicio_Contable
(
	EjercicioContableId uniqueidentifier NOT NULL,
	Anio VARCHAR(50),
	Detalle VARCHAR(50),
	EstaActivo bit,
	EmpresaId uniqueidentifier NOT NULL,
	PRIMARY KEY (EjercicioContableId),
	FOREIGN KEY (EmpresaId) REFERENCES common_Empresa(EmpresaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;



CREATE TABLE common_Cliente
(
	ClienteId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	Nombres VARCHAR(50),
	DniRuc VARCHAR(50),
	Direccion VARCHAR(50),
	DireccionRef VARCHAR(50),
	Procedencia VARCHAR(50),
	Fonos VARCHAR(50),
	
	Email VARCHAR(50),
	EstaActivo bit,
	EstaAfectoARetencion bit,
	EstaAfectoDePercepcion bit,
	EsCuenta bit,

	SiSo float,
	SiDo float,
	SiGr float,
	DocFiscalOperacion VARCHAR(50),
	
	AgenciaId uniqueidentifier NOT NULL,
	UNIQUE(AgenciaId, Codigo),
	PRIMARY KEY (ClienteId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;

CREATE TABLE common_Proveedor
(
	ProveedorId uniqueidentifier NOT NULL,
	Codigo VARCHAR(50),
	RazonSocial VARCHAR(50),
	RucDni VARCHAR(50),
	Direccion VARCHAR(50),
	Fonos VARCHAR(50),
	Email VARCHAR(50),
	EstaActivo bit,
	EstaAfectoARetencion bit,
	EstaAfectoDePercepcion bit,
	AgenciaId uniqueidentifier NOT NULL,
	UNIQUE(AgenciaId, Codigo),
	PRIMARY KEY (ProveedorId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
) ;

CREATE TABLE common_Tipo_Cambio
(
	TipoCambioId uniqueidentifier NOT NULL,
	Fecha DATETIME,
	Compra float,
	Venta float,
	EmpresaId uniqueidentifier NOT NULL,
	UNIQUE(EmpresaId,Fecha),
	PRIMARY KEY (TipoCambioId),
	FOREIGN KEY (EmpresaId) REFERENCES common_Empresa(EmpresaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
) ;


CREATE TABLE common_Correlativo_Entidad
(
	CorrelativoEntidadId uniqueidentifier NOT NULL,
	Contador uniqueidentifier,
	Entidad VARCHAR(50),
	AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (CorrelativoEntidadId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;
